package model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;

import structures.Project;
import structures.ProjectListEntry;
import structures.Task;
import structures.KvItem;

//import com.mysql.jdbc.Connection;

/**
 *  My implementation of the model.
 * @author darin
 *
 * SQl queries should return a 2d array of Object[][] that contain the data
 */
public class MyProjectDbModel extends ProjectDBModel {
	/**
	 * A single always open connection to the database;
	 * will use later if I decide that handling all the exceptions
	 * each time a sql query is made is too annoying
	 */
	//private Connection con;

	/**
	 * The database host to connect to.
	 */
	private String host = "jdbc:mysql://localhost:3306/";
	/**
	 * the database to use.
	 */
	private String db = "projectDB";
	/**
	 * the programs db user name.
	 */
	private String username = "projectDB"; //ProjectDB
	/**
	 * the programs db  password.
	 */
	private String password = "projectdb"; //projectdb

	/**
	 * performs initial setup.
	 */
	public MyProjectDbModel() {
		// get a connection to the model
		//populate categories list
		//populate sub-categories list
		//populate a list of projects
	}

	/**
	 * GetConnection() gets a connection to the database.
	 * @return a connection to the database
	 * @throws ClassNotFoundException if class not found?
	 * @throws IllegalAccessException if illegal access?
	 * @throws InstantiationException why?
	 * @throws SQLException if there's an sql error?
	 */
	private Connection getConnection() throws InstantiationException,
	IllegalAccessException, ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		  Connection con = DriverManager.getConnection(host + db,
				  username, password);
		  return con;
	}

	/**
	 * @param name String the name of the project
	 * @param desc String the description of the project
	 * @param date String YYYY-MM-dd
	 * @throws IllegalAccessException when?
	 * @throws ClassNotFoundException when?
	 * @throws SQLException when?
	 * @throws InstantiationException when?
	 */
	public final void createNewProject(final String name,
			final String desc, final String date)
			throws  IllegalAccessException, ClassNotFoundException,
			SQLException, InstantiationException {
		Connection con = getConnection();
		String sql = "insert into Project "
		+ "(project_name, "
			+ "project_desc, project_date)"
			+ "values(?, ?, ?)";
		PreparedStatement statement = con.prepareStatement(sql);
		statement.setString(1, name);
		statement.setString(2, desc);
		statement.setString(3, date);
		statement.execute();
		con.close();
		setChanged();
		notifyObservers("Projects");
	}

	/**
	 * @return list of project ids and names.
	 * @throws InstantiationException on error making connection to db
	 * @throws IllegalAccessException on error connecting to db
	 * @throws ClassNotFoundException on error connecting to db
	 * @throws SQLException on bad sql statement
	 */
	public final ArrayList<ProjectListEntry> getProjectNames()
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException {
		ArrayList<ProjectListEntry> list =
				new ArrayList<ProjectListEntry>();

		String sql = ("Select * from Project");
		Connection con = getConnection();
		PreparedStatement statement = con.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			ProjectListEntry e = new ProjectListEntry(
					resultSet.getInt("project_id"),
					resultSet.getString("project_name"));
			list.add(e);
		}
		con.close();
		return list;
	}

	/**
	 * @param id the id of the project to fetch from the db
	 * @return a project populated with data from the db
	 * @throws InstantiationException on error making connection to db
	 * @throws IllegalAccessException on error connecting to db
	 * @throws ClassNotFoundException on error connecting to db
	 * @throws SQLException on bad sql statement
	 *
	 */
	public final Project getProject(final int id)
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException {

		Connection con = getConnection();
		String sql = ("SELECT * FROM Project WHERE project_id = ?");
		PreparedStatement statement = con.prepareStatement(sql);
		statement.setInt(1, id);
		ResultSet resultSet = statement.executeQuery();
		resultSet.first();
		Project newProject = new Project(
				resultSet.getInt("project_id"),
				resultSet.getString("project_name"),
				resultSet.getString("project_desc"),
				resultSet.getDate("project_date"));

		con.close();
		return newProject;
	}

	/**
	 * @param id the id of the project to delete
	 * @throws InstantiationException on error making connection to db
	 * @throws IllegalAccessException on error connecting to db
	 * @throws ClassNotFoundException on error connecting to db
	 * @throws SQLException on bad sql statement
	 *
	 */
	public final void deleteProject(final int id)
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException {
		Connection con = getConnection();
		String sql = ("DELETE FROM Project WHERE project_id = ?");
		PreparedStatement statement = con.prepareStatement(sql);
		statement.setInt(1, id);
		statement.execute();
		con.close();
		setChanged();
		notifyObservers("Projects");
	}


	/**
	 * @param projectId the id of the project to fetch tasks for.
	 * @throws InstantiationException on error making connection to db
	 * @throws IllegalAccessException on error connecting to db
	 * @throws ClassNotFoundException on error connecting to db
	 * @throws SQLException on bad sql statement
	 * @return ArrayList<Task> of tasks belonging to this project
	 */
	public final  ArrayList<Task> getTasks(final int projectId)
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException {
		Connection con = getConnection();
		String sql = ("SELECT * FROM Task WHERE project_id = ?");

		PreparedStatement statement = con.prepareStatement(sql);
		statement.setInt(1, projectId);
		ResultSet resultSet = statement.executeQuery();
		ArrayList<Task> list = new ArrayList<Task>();
		while (resultSet.next()) {
			Task task = new Task();
			task.setProjectId(resultSet.getInt("project_id"));
			task.setTaskId(resultSet.getInt("task_id"));
			task.setType(resultSet.getInt("type_id"));
			task.setSubtype(resultSet.getInt("subtype_id"));
			task.setTaskDesc(resultSet.getString("task_desc"));
			task.setTaskDate(resultSet.getDate("task_date"));
			task.setTimeIn(resultSet.getTime("time_in"));
			task.setTimeOut(resultSet.getTime("time_out"));
			list.add(task);
		}
		con.close();
	return list;
	}

	public final  Object[] getTypes()
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException {
		Connection con = getConnection();
		String sql = ("SELECT * FROM Type");
		PreparedStatement statement = con.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		ArrayList<KvItem> types = new ArrayList<KvItem>();
		while (resultSet.next()) {
			int key = resultSet.getInt("type_id");
			String value = resultSet.getString("type_desc");
			KvItem type = new KvItem(key, value);
			types.add(type);
		}
		return types.toArray();
	}

	public final  Object[] getSubTypes()
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException {
		Connection con = getConnection();
		String sql = ("SELECT * FROM Subtype");
		PreparedStatement statement = con.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();

		ArrayList<KvItem> types = new ArrayList<KvItem>();

		while (resultSet.next()) {
			int key = resultSet.getInt("subType_id");
			String value = resultSet.getString("subType_desc");
			KvItem subType = new KvItem(key, value);
			types.add(subType);
		}
		return types.toArray();
	}

	public final HashMap<Integer, String> getTypeMap()
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException
	{
		HashMap<Integer, String> typeMap = new HashMap<Integer, String>();
		Connection con = getConnection();
		String sql = ("SELECT * FROM Type");
		PreparedStatement statement = con.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			typeMap.put(resultSet.getInt("type_id"),
					resultSet.getString("type_desc"));
		}
		return typeMap;
	}
	public final HashMap<Integer, String> getSubtypeMap()
			throws InstantiationException,
			IllegalAccessException,
			ClassNotFoundException,
			SQLException
	{
		HashMap<Integer, String> subtypeMap = new HashMap<Integer, String>();
		Connection con = getConnection();
		String sql = ("SELECT * FROM Subtype");
		PreparedStatement statement = con.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			subtypeMap.put(resultSet.getInt("subType_id"),
					resultSet.getString("subType_desc"));
		}
		return subtypeMap;
	}

	public final void updateTask(final Task task)
			throws  IllegalAccessException,
			ClassNotFoundException,
			SQLException, InstantiationException {
		Connection con = getConnection();
		String sql = "UPDATE Task SET type_id = ?, subtype_id = ?," +
				"task_desc = ?, task_date = ?, time_in = ?, time_out = ?" +
				" where (project_id = ? AND task_id = ?)";
		PreparedStatement statement = con.prepareStatement(sql);
		statement.setInt(1, task.getType());
		statement.setInt(2, task.getSubtype());
		statement.setString(3, task.getTaskDesc());
		statement.setDate(4, task.getTaskDate());
		statement.setTime(5, task.getTimeIn());
		statement.setTime(6, task.getTimeOut());
		statement.setInt(7,task.getProjectId());
		statement.setInt(8, task.getTaskId());
		statement.execute();
		con.close();
		setChanged();
		notifyObservers("Tasks");
	}
	
	public final void insertTask(final Task task)
			throws  IllegalAccessException,
			ClassNotFoundException,
			SQLException, InstantiationException {
		Connection con = getConnection();
		String sql = "INSERT INTO Task (project_id, type_id, "
		+ "subtype_id, task_desc, task_date, time_in, time_out)"
				+ " VALUES (?,?,?,?,?,?,?)";
		PreparedStatement statement = con.prepareStatement(sql);
		statement.setInt(1, task.getProjectId());
		statement.setInt(2, task.getType());
		statement.setInt(3, task.getSubtype());
		statement.setString(4, task.getTaskDesc());
		statement.setDate(5, task.getTaskDate());
		statement.setTime(6, task.getTimeIn());
		statement.setTime(7, task.getTimeOut());
		statement.execute();
		con.close();
		setChanged();
		notifyObservers("Tasks");
	}

	public void deleteTask(int taskId)
			throws  IllegalAccessException,
			ClassNotFoundException,
			SQLException, InstantiationException{
		Connection con = getConnection();
		String sql = "DELETE FROM Task WHERE task_id = ?";
		PreparedStatement statement = con.prepareStatement(sql);
		statement.setInt(1,taskId);
		statement.execute();
		con.close();
		setChanged();
		notifyObservers("Tasks");
	}
}
